package com.my.apacheDBUtils;

import com.my.entity.Student;
import com.my.user.util.DataSourceUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import java.util.Map;

public class Test {
    public static void testArrayHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSourceWithC3P0ByXML());
        Object[] student = runner.query("select * from student where id > ?",new ArrayHandler(),1);//ArrayHandler在结果中只取一行
        System.out.println(student[0] + "," + student[1] + "," + student[2]);
    }

    public static void testArrayListHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSourceWithC3P0ByXML());
        List<Object[]> students = runner.query("select * from student where id > ?",new ArrayListHandler(),1);//ArrayHandler在结果中只取一行
        for(Object[] student: students){
            System.out.println(student[0] + "," + student[1] + "," + student[2]);
        }
    }

    //用对象接收，通过反射来创建对象，反射通过无参构造创建对象，所以实体类需要有无参构造方法
    public static void testBeanHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSourceWithC3P0ByXML());
        Student student = runner.query("select * from student where id > ?",new BeanHandler<Student>(Student.class),1);//ArrayHandler在结果中只取一行
        System.out.println(student.getId() + "," + student.getName() + "," + student.getAge());
    }

    public static void testBeanListHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSourceWithC3P0ByXML());
        List<Student> students = runner.query("select * from student where id > ?",new BeanListHandler<Student>(Student.class),1);//ArrayHandler在结果中只取一行
        for(Student student: students){
            System.out.println(student.getId() + "," + student.getName() + "," + student.getAge());
        }
    }

    public static void testBeanMapHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSourceWithC3P0ByXML());
        Map<Integer,Student> students = runner.query("select * from student where id > ?",new BeanMapHandler<Integer,Student>(Student.class,"id"),1);//ArrayHandler在结果中只取一行
        Student student = students.get(2);
        System.out.println(student.getId() + "," + student.getName() + "," + student.getAge());
    }

    public static void testMapHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSourceWithC3P0ByXML());
        Map<String,Object> student = runner.query("select * from student where id > ?",new MapHandler(),1);//ArrayHandler在结果中只取一行
        System.out.println(student);
    }

    public static void testMapListHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSourceWithC3P0ByXML());
        List<Map<String,Object>> students = runner.query("select * from student where id > ?",new MapListHandler(),1);//ArrayHandler在结果中只取一行
        System.out.println(students);
    }

    public static void testKeyedHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSourceWithC3P0ByXML());
        Map<String,Map<String,Object>> students = runner.query("select * from student where id > ?",new KeyedHandler<String>("name"),1);//ArrayHandler在结果中只取一行
        System.out.println(students);
    }

    public static void testColumnListHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSourceWithC3P0ByXML());
        List<String> students = runner.query("select * from student where id > ?",new ColumnListHandler<String>("name"),1);
        System.out.println(students);
    }

    public static void testScalarHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSourceWithC3P0ByXML());
        //Long count = runner.query("select count(1) from student where id > ?",new ScalarHandler<Long>(),1);
        String name = runner.query("select name from student where id = ?",new ScalarHandler<String>(),1);
        System.out.println(name);
    }


    public static void main(String[] args) throws SQLException {
        //testBeanListHandler();
        testScalarHandler();
    }
}
